package com.lee

import grails.converters.JSON
import grails.transaction.Transactional
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory

import java.time.LocalDate

import static org.springframework.http.HttpStatus.*

@Transactional
class ExcelController {

    def excelImportService

    /**
     *  从指定行startRow开始读取，startRow从0开始算
     *  不指定lastRow，导入从指定行开始到最后一行的数据
     */
    def excelImport = {
        InputStream inputStream = null
        try {
            //获取文件，此处可通过上传获取文件
            File file = new File("c:\\file\\用户信息.xlsx")
            //定义表头
            Map CONFIG_BOOK_COLUMN_MAP = [
                    sheet:'Sheet1',
                    startRow: 1,
                    columnMap:  [
                            //Col, Map-Key
                            'A':'name',
                            'B':'age',
                            'C':'sex',
                            'D':'addr',
                            'E':'birth',
                            'F':'weight'
                    ]
            ]
            //将文件放到输入流中
            inputStream = new FileInputStream(file)
            Workbook workbook = WorkbookFactory.create(inputStream)

            //解析Excel的行存入list
            List list = excelImportService.columns(workbook,CONFIG_BOOK_COLUMN_MAP)
            list.each { v->
                User user = new User()
                user.name = v.name
                user.age = v.age as Integer
                user.sex = v.sex
                user.addr = v.addr
                user.birth = v.birth.toDate() //org.joda.time.LocalDate.toDate()转为日期类型
                user.weight = v.weight
                user.save(flush:true)
                println (user as JSON)
            }
            render ([msg:"Excel导入成功"]) as JSON
        } catch (Exception e) {
            e.printStackTrace()
            render ([msg:"Excel导入失败"]) as JSON
        }finally{
            //关闭输入流
            if(!inputStream){
                inputStream.close()
            }
        }
    }

    /**
     *  从指定行startRow开始读取，截止到lastRow最后行
     *  startRow的下标从0开始
     *  lastRow下标从1开始
     *  lastRow必须大于startRow(lastRow > startRow)，若lastRow==-1，则读取指定行开始之后的所有数据
     */
    def rangeImport = {
        InputStream inputStream = null
        try {
            //获取文件，此处可通过上传获取文件
            File file = new File("c:\\file\\用户信息.xlsx")
            //定义表头
            Map CONFIG_BOOK_COLUMN_MAP = [
                    sheet:'Sheet1',
                    startRow: 1,
                    lastRow: 3,
                    columnMap:  [
                            //Col, Map-Key
                            'A':'name',
                            'B':'age',
                            'C':'sex',
                            'D':'addr',
                            'E':'birth',
                            'F':'weight'
                    ]
            ]
            //将文件放到输入流中
            inputStream = new FileInputStream(file)
            Workbook workbook = WorkbookFactory.create(inputStream)

            //解析Excel的行存入list
            List list = excelImportService.columns(workbook,CONFIG_BOOK_COLUMN_MAP)
            list.each { v->
                User user = new User()
                user.name = v.name
                user.age = v.age as Integer
                user.sex = v.sex
                user.addr = v.addr
                user.birth = v.birth.toDate()
                user.weight = v.weight
                user.save(flush:true)
                println (user as JSON)
            }
            render ([msg:"Excel导入成功"]) as JSON
        } catch (Exception e) {
            e.printStackTrace()
            render ([msg:"Excel导入失败"]) as JSON
        }finally{
            //关闭输入流
            if(!inputStream){
                inputStream.close()
            }
        }
    }
}
